package m.system.db;

import m.common.model.util.QueryParameter;
import m.system.RuntimeData;
import m.system.util.ArrayUtil;
import m.system.util.DateUtil;
import m.system.util.SystemUtil;
import manage.run.ServerFilter;

import java.sql.*;
import java.util.*;

/**
 * 数据库操作类
 * @author Administrator
 *
 */
public class DBManager {
	private static void outSqlAndParams(Long timeLong,DBOper method,DBResult result,String sql,List<Object[]> params){
		ServerFilter.threadInstance().addSqlAndParam(timeLong,method,result,new String[]{sql},new Object[]{"本次批处理"+params.size()+"条数据。"});
		if(RuntimeData.getDebug()) {
			SystemUtil.println(timeLong,"ms, ",result," ",method, " Preparing: ", sql);
			if (null == params) return;
			for (int i=0;i<params.size();i++) {
				if(i>10){
					SystemUtil.println("\t\t超过10行的参数不打印...");
					break;
				}
				Object[] ps=params.get(i);
				if (null == ps || ps.length == 0) continue;
				SystemUtil.println("\t\tParameters: ",outParams(ps));
			}
		}
	}
	private static void outSqlAndParams(Long timeLong,DBOper method,DBResult result,String sql,Object[] params){
		ServerFilter.threadInstance().addSqlAndParam(timeLong,method,result,new String[]{sql},params);
		if(RuntimeData.getDebug()) {
			SystemUtil.println(timeLong,"ms, ",result," ",method, " Preparing: ", sql);
			if (null == params) return;
			SystemUtil.println("\t\tParameters: ",outParams(params));
		}
	}
	private static void outSqls(Long timeLong,DBOper method,DBResult result,String[] sqls){
		ServerFilter.threadInstance().addSqlAndParam(timeLong,method,result,sqls,new Object[0]);
		if(RuntimeData.getDebug()) {
			SystemUtil.println(timeLong,"ms, ",result," ",method);
			for(String sql : sqls){
				SystemUtil.println("\tPreparing: ", sql);
			}
		}
	}
	public static String outParams(Object[] ps){
		StringBuilder sb=new StringBuilder();
		for (int i = 0; i < ps.length; i++) {
			Object obj = ps[i];
			if (i != 0) sb.append(", ");
			if (null == obj) sb.append("null(Null)");
			else if (obj instanceof Integer) sb.append(obj).append("(Integer)");
			else if (obj instanceof Long) sb.append(obj).append("(Long)");
			else if (obj instanceof Double) sb.append(obj).append("(Double)");
			else if (obj instanceof Float) sb.append(obj).append("(Float)");
			else if (obj instanceof Boolean) sb.append(obj).append("(Boolean)");
			else if (obj instanceof java.util.Date)
				sb.append(DateUtil.format((java.util.Date) obj, DateUtil.YYYY_MM_DD_HH_MM_SS)).append("(Date)");
			else if(obj instanceof Object[]) sb.append(ArrayUtil.connection((Object[])obj,",")).append("(String)");
			else sb.append(obj).append("(String)");
		}
		return sb.toString();
	}
	/**
	 * 插入sql，返回自增列id
	 */
	public static Integer executeInsert(String sql) throws SQLException {
		return executeInsert(sql,new Object[]{});
	}
	/**
	 * 插入sql，返回自增列id
	 */
	public static Integer executeInsert(String sql, Object... params) throws SQLException {
		return executeInsert(sql,params,DBConfig.getQueryTimeout());
	}
	/**
	 * 插入sql，返回自增列id
	 */
	public static Integer executeInsert(String sql,Object[] params,int maxSeconds) throws SQLException{
		long t=System.currentTimeMillis();
		Connection conn;
		PreparedStatement ps=null;
		Integer id=null;
		try {
			conn = TransactionManager.getConnection();
			ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			ps.setQueryTimeout(maxSeconds);
			// 参数赋值
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			// 执行
			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			while (rs.next()){
				//生成的主键只有一个值，所以获得第一列的值即可
				id = rs.getInt(1);
			}
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.Insert,DBResult.Success,sql,params);
		} catch (SQLException e){
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.Insert,DBResult.Error,sql,params);
			throw e;
		} finally {
			// 释放资源
			if(null!=ps) ps.close();
		}
		return id;
	}

	/**
	 * 更新sql（可插入，修改或者删除） 返回影响行数
	 */
	public static int executeUpdate(String sql) throws SQLException{
		return executeUpdate(sql,new Object[]{});
	}
	/**
	 * 更新sql（可插入，修改或者删除） 返回影响行数
	 */
	public static int executeUpdate(String sql, Object... params) throws SQLException {
		return executeUpdate(sql,params,DBConfig.getQueryTimeout());
	}
	/**
	 * 更新sql（可插入，修改或者删除） 返回影响行数
	 */
	public static int executeUpdate(String sql, Object[] params, int maxSeconds) throws SQLException {
		long t=System.currentTimeMillis();
		Connection conn;
		PreparedStatement ps=null;
		int num;
		try {
			conn = TransactionManager.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setQueryTimeout(maxSeconds);
			// 参数赋值
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			// 执行
			num = ps.executeUpdate();
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.Update,DBResult.Success,sql,params);
		} catch (SQLException e){
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.Update,DBResult.Error,sql,params);
			throw e;
		} finally {
			// 释放资源
			if(null!=ps) ps.close();
		}
		return num;
	}
	/**
	 * 批量更新（可插入，修改或者删除） 无返回
	 */
	public static void batchUpdate(String sql, List<Object[]> paramList) throws SQLException{
		batchUpdate(sql,paramList,DBConfig.getQueryTimeout());
	}
	/**
	 * 批量更新（可插入，修改或者删除） 无返回
	 */
	public static void batchUpdate(String sql, List<Object[]> paramList,int maxSeconds) throws SQLException{
		long t=System.currentTimeMillis();
		Connection conn;
		PreparedStatement ps=null;
		try {
			conn=TransactionManager.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setQueryTimeout(maxSeconds);
			int m=0;
			// 参数赋值
			for(int n=0,len=paramList.size();n<len;n++){
				if(n%10000==0&&m>0){
					ps.executeBatch();
					m=0;
					ps=conn.prepareStatement(sql);
					ps.setQueryTimeout(maxSeconds);
				}
				Object[] params=paramList.get(n);
				if (params!=null) {
					for (int i = 0; i < params.length; i++) {
						ps.setObject(i + 1, params[i]);
					}
					ps.addBatch();
					m++;
				}
			}
			// 执行
			if(m>0) ps.executeBatch();
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.BatchUpdate,DBResult.Success,sql,paramList);
		} catch (SQLException e){
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.BatchUpdate,DBResult.Error,sql,paramList.toArray(new Object[0]));
			throw e;
		} finally {
			// 释放资源
			if(null!=ps) ps.close();
		}
	}
	/**
	 * 不同sql语句的批量更新（可插入，修改或者删除） 无返回
	 */
	public static void batchUpdate(String[] sqls) throws SQLException{
		batchUpdate(sqls,DBConfig.getQueryTimeout());
	}
	/**
	 * 不同sql语句的批量更新（可插入，修改或者删除） 无返回
	 */
	public static void batchUpdate(String[] sqls,int maxSeconds) throws SQLException{
		long t=System.currentTimeMillis();
		Connection conn;
		Statement ps=null;
		try {
			conn=TransactionManager.getConnection();
			ps=conn.createStatement();
			ps.setQueryTimeout(maxSeconds);
			// 参数赋值
			for(String sql : sqls){
				if (sql!=null&& !sql.trim().isEmpty()) {
					ps.addBatch(sql.trim());
				}
			}
			// 执行
			ps.executeBatch();
			outSqls(System.currentTimeMillis()-t,DBOper.BatchSql,DBResult.Success,sqls);
		} catch (SQLException e){
			outSqls(System.currentTimeMillis()-t,DBOper.BatchSql,DBResult.Error,sqls);
			throw e;
		} finally {
			// 释放资源
			if(null!=ps) ps.close();
		}
	}

	/**
	 * 执行查询语句 返回dataset
	 */
	public static DataSet executeQuery(QueryParameter qp) throws SQLException {
		return executeQuery(qp.getSql(), qp.getValueList().toArray(new Object[] {}));
	}
	/**
	 * 执行查询语句 返回dataset
	 */
	public static DataSet executeQuery(String sql) throws SQLException {
		return executeQuery(sql,null);
	}
	/**
	 * 执行查询语句 返回dataset
	 */
	public static DataSet executeQuery(String sql, Object[] params) throws SQLException {
		return executeQuery(sql,params,DBConfig.getQueryTimeout());
	}
	/**
	 * 执行查询语句 返回dataset
	 */
	public static DataSet executeQuery(String sql, Object[] params,int maxSeconds) throws SQLException {
		long t=System.currentTimeMillis();
		Connection conn;
		PreparedStatement ps=null;
		ResultSetMetaData rsmd;
		ResultSet rs=null;
		int columnCount;
		List<Object> list = new ArrayList<>();
		try {
			conn=TransactionManager.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setQueryTimeout(maxSeconds);
			if (params!=null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			// 执行SQL获得结果集
			rs = ps.executeQuery();
			rsmd = rs.getMetaData();
			// 获得结果集列数
			columnCount = rsmd.getColumnCount();
			// 将ResultSet的结果保存到List中
			while (rs.next()) {
				Map<String, Object> map = new HashMap<>();
				for (int i = 1; i <= columnCount; i++) {
					map.put(rsmd.getColumnLabel(i), rs.getObject(i));
				}
				list.add(map);
			}
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.Query,DBResult.Success,sql,params);
		} catch (SQLException e){
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.Query,DBResult.Error,sql,params);
			throw e;
		} finally {
			// 释放资源
			if(null!=ps) ps.close();
			if(null!=rs) rs.close();
		}
		return new DataSet(list);
	}
	/**
	 * 执行单行结果的查询语句 返回datarow
	 */
	public static DataRow queryFirstRow(String sql) throws SQLException{
		return queryFirstRow(sql,new Object[]{});
	}
	/**
	 * 执行单行结果的查询语句 返回datarow
	 */
	public static DataRow queryFirstRow(String sql, Object... params) throws SQLException{
		return queryFirstRow(sql,params,DBConfig.getQueryTimeout());
	}
	/**
	 * 执行单行结果的查询语句 返回datarow
	 */
	public static DataRow queryFirstRow(String sql, Object[] params,int maxSeconds) throws SQLException{
		long t=System.currentTimeMillis();
		if(!sql.toLowerCase().contains("limit")) sql= sql + " LIMIT 1";
		Connection conn;
		PreparedStatement ps=null;
		ResultSetMetaData rsmd;
		ResultSet rs=null;
		int columnCount;
		Map<String, Object> map=null;
		try {
			conn=TransactionManager.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setQueryTimeout(maxSeconds);
			if (params!=null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			// 执行SQL获得结果集
			rs = ps.executeQuery();
			rsmd = rs.getMetaData();
			// 获得结果集列数
			columnCount = rsmd.getColumnCount();
			// 将ResultSet的结果保存到List中
			if (rs.next()) {
				map = new HashMap<>();
				for (int i = 1; i <= columnCount; i++) {
					map.put(rsmd.getColumnLabel(i), rs.getObject(i));
				}
			}
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.First,DBResult.Success,sql,params);
		} catch (SQLException e){
			outSqlAndParams(System.currentTimeMillis()-t,DBOper.First,DBResult.Error,sql,params);
			throw e;
		} finally {
			// 释放资源
			if(null!=ps) ps.close();
			if(null!=rs) rs.close();
		}
		return null!=map?new DataRow(map):null;
	}
}
